package com.kailong.dao.admin;

import com.kailong.dao.BaseDao;
import com.kailong.pojo.Seat;
import com.kailong.pojo.Student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class ManageSeatDao {
    public static  int insert(Seat s){
        String sql = "insert into seat values (?,?,?,?,?)";
        Object [] params = {
                s.getTno(),
                s.getTfloor(),
                s.getTsquare(),
                s.getTstate(),
                s.getTinfo()
        };
        return BaseDao.exectuIUD(sql,params);
    }

    /**
     * 获取总记录数和总页数
     * @param count
     * @return
     */
    public  static int[] totalPage(int count,String floor,String square){
        //0,总记录数1,总页数
        int arr[] = {0,1};
        Connection conn = BaseDao.getconn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "";
            if(floor != null && square!= null){
                sql = "select count(*) from seat where tfloor = ? and tsquare = ? ";
                ps = conn.prepareStatement(sql);
                ps.setString(1,floor);
                ps.setString(2,square);

            }else {
                sql = "select count(*) from seat";
                ps = conn.prepareStatement(sql);
            }

            rs = ps.executeQuery();
            while(rs.next()){
                arr[0] = rs.getInt(1);
                if(arr[0] / count == 0){
                    arr[1] = arr[0]/count;
                }else {
                    arr[1] = arr[0]/count + 1;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeall(rs,ps,conn);
        }
        return  arr;
    }
    public static ArrayList<Seat> selectAll(int cpage,int count,String floor,String square,String tstate){
        ArrayList<Seat> list = new ArrayList<>();
        //声明结果集
        ResultSet rs = null;
        //获取连接对象
        Connection conn = BaseDao.getconn();

        PreparedStatement ps = null;
        try{
            String sql = "";
            if (floor!=null && square!=null && tstate!=null){
                sql = "select * from seat where tfloor = ? and tsquare = ? and tstate = ? order by tno  limit ?,?";
                ps = conn.prepareStatement(sql);
                ps.setString(1,floor);
                ps.setString(2,square);
                ps.setString(3,tstate);
                ps.setInt(4,(cpage-1)*count);
                ps.setInt(5,count);
            }else{
                sql = "select * from seat order by tno desc limit ?,?";
                ps = conn.prepareStatement(sql);

                ps.setInt(1,(cpage-1)*count);
                ps.setInt(2,count);
            }
            rs = ps.executeQuery();
            while (rs.next()){
                Seat s = new Seat(
                        rs.getInt("tno"),
                        rs.getString("tfloor"),
                        rs.getString("tsquare"),
                        rs.getString("tstate"),
                        rs.getString("tinfo")
                );
                list.add(s);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            BaseDao.closeall(rs,ps,conn);
        }


        return  list;
    }

    /**
     * 通过tno查找相关座位
     * @param tno
     * @return
     */
    public static Seat selectByTno(String tno){
        Seat seat = null;
        //声明结果集
        ResultSet rs = null;
        //获取连接对象
        Connection conn = BaseDao.getconn();

        PreparedStatement ps = null;
        try{
            String sql = "select * from seat where tno = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,tno);
            rs = ps.executeQuery();
            while (rs.next()){
                seat = new Seat(
                        rs.getInt("tno"),
                        rs.getString("tfloor"),
                        rs.getString("tsquare"),
                        rs.getString("tstate"),
                        rs.getString("tinfo")
                );

            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            BaseDao.closeall(rs,ps,conn);
        }


        return  seat;
    }
    public static  int update(Seat s){
        String sql = "update seat set tfloor = ?,tsquare = ?,tstate = ?,tinfo = ? where tno = ?";
        Object [] params = {

                s.getTfloor(),
                s.getTsquare(),
                s.getTstate(),
                s.getTinfo(),
                s.getTno()
        };
        return BaseDao.exectuIUD(sql,params);
    }
    public static int del(String tno){
        String  sql = "delete from seat where tno = ?";
        Object [] params = { tno};
       return BaseDao.exectuIUD(sql,params);
    }

}
